package com.hd123.rumba.qt.run;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.Session;
import net.sf.hibernate.Transaction;

import org.apache.log4j.Logger;

import com.hd123.rumba.domain.IDomain;
import com.hd123.rumba.exception.BusinessException;
import com.hd123.rumba.qt.IQueryMgr;
import com.hd123.rumba.qt.QTConstants;
import com.hd123.rumba.qt.QueryResultSession;
import com.hd123.rumba.qt.result.IQueryResultMgr;
import com.hd123.rumba.qt.result.QueryResultDescription;
import com.hd123.rumba.runtime.HibernateSession;
import com.hd123.rumba.runtime.NamingMgr;
import com.hd123.rumba.util.HashMap2;
import com.hd123.rumba.util.ObjectUtil;
import com.hd123.rumba.util.StopWatch;
import com.hd123.rumba.util.StringUtil;

public class QueryThread extends Thread {

  private Session sourceSession;
  private Session resultSession;
  private QueryResultDescription qrd;
  private Logger logger = Logger.getLogger(QueryThread.class);
  private IDomain currentDomain;

  /**
   * 中断标志
   */
  private boolean bStop = false;
  /**
   * beforerun脚本和select语句在这个stmt上执行, 以便被中断
   */
  private Statement stmt;

  private static IQueryMgr getQueryMgr() throws BusinessException {
    return (IQueryMgr) NamingMgr.getInstance().lookupMgr(IQueryMgr.class);
  }

  public QueryThread(Session sourceSession, Session resultSession, QueryResultDescription qrd)
      throws BusinessException, HibernateException {
    super(getQueryMgr().getQtGroup(), StringUtil.createUuid());
    this.sourceSession = sourceSession;
    this.resultSession = resultSession;
    this.qrd = qrd;
  }

  public void run() {
    try {
      StopWatch sw = new StopWatch().start();
      beforerun();
      String message = null;
      int resultCount = 0;
      try {
        resultCount = dorun();
        // this.qrd.setTimeUsed(sw.getTime());
      } catch (Exception e) {
        message = e.getMessage();
        resultCount = QueryResultDescription.RESULTCOUNT_ERROR.intValue();
        logger.warn("run:dorun: " + ObjectUtil.getErrorTrace(e));
      }
      afterrun(resultCount, new Long(sw.stop()), message);
    } catch (Exception e) {
      logger.warn("run: " + ObjectUtil.getErrorTrace(e));
    } finally {
      try {
        sourceSession.close();
        resultSession.close();
      } catch (HibernateException e) {
        Logger.getLogger(QueryThread.class).error("run:finally: " + ObjectUtil.getErrorTrace(e));
      }
    }
  }

  private void beforerun() throws HibernateException {

  }

  private void afterrun(int resultCount, Long timeUsed, String message) throws BusinessException,
      HibernateException {
    IQueryResultMgr qrm = (IQueryResultMgr) NamingMgr.getInstance()
        .lookupMgr(IQueryResultMgr.class);
    QueryResultDescription qrd = qrm.findQrdByUuid(this.qrd.getUuid());
    if (qrd == null) {
      logger.error("qrd not found: uuid=" + this.qrd.getUserUuid() + " qrd.threadId="
          + this.qrd.getThreadId() + " thread id=" + getId());
    }
    qrd.setResultCount(new Integer(resultCount));
    qrd.setThreadId(new Long(0));
    qrd.setTimeUsed(timeUsed);
    qrd.setMessage(message);
    try {
      Transaction tx = HibernateSession.beginTransaction();
      try {
        qrm.saveQrd(qrd);
        HibernateSession.commit(tx);
      } catch (HibernateException e) {
        HibernateSession.rollback(tx);
        throw e;
      }
    } finally {
      HibernateSession.closeSession();
    }
  }

  /**
   * @return 结果集记录数. 如果是用户取消, 返回QueryResultDescription.RESULTCOUNT_CANCEL
   * @throws Exception
   */
  private int dorun() throws Exception {
    StopWatch sw = new StopWatch();
    int resultCount = QueryResultDescription.RESULTCOUNT_RUNNING.intValue();
    stmt = sourceSession.connection().createStatement();
    if (!bStop) {
      sw.start();
      if (qrd.getQuery().getBeforeRunScript() != null)
        qrd.getQuery().getBeforeRunScript().execute(sourceSession.connection(), this);
      logger.info("perf|" + sw.stop() + "|before run");
    }
    if (!bStop) {
      sw.start();
      resultCount = saveResult(sourceSession.connection(), qrd.getQuery().getSQLClause(),
          resultSession.connection(), qrd.getTableName());
      logger.info("perf|" + sw.stop() + "|save result");
    }
    if (!bStop) {
      sw.start();
      if (qrd.getQuery().getAfterRunScript() != null)
        qrd.getQuery().getAfterRunScript().execute(sourceSession.connection(), this);
      logger.info("perf|" + sw.stop() + "|after run");
    }
    if (bStop)
      resultCount = QueryResultDescription.RESULTCOUNT_CANCEL.intValue();
    return resultCount;
  }

  /**
   * 在sourceConnection上执行selectStatement, 结果存入resultConnection的tableName表中.
   * 如果tableName表存在, 则删除之并重建.
   * 
   * @param sourceConnection
   * @param selectStatement
   * @param resultConnection
   */
  public int saveResult(Connection sourceConnection, String selectStatement,
      Connection resultConnection, String tableName) throws SQLException {
    logger.debug("\n" + selectStatement);
    int resultCount = 0;
    ResultSet rs = null;
    Statement resultDdlStmt = resultConnection.createStatement();
    PreparedStatement resultInsertStmt = null;
    try {
      try {
        synchronized (stmt) {
          if (ObjectUtil.dbtypeOracle == sourceDbType) {
            stmt
                .execute("ALTER SESSION SET NLS_DATE_FORMAT=" + QTConstants.QUERY_ORACLE_DATEFORMAT);
          }
          rs = stmt.executeQuery(selectStatement);
        }
      } catch (SQLException e) {
        // if caused by stmt.cancel(), don't report it.
        if (!bStop)
          throw e;
      }
      if (!bStop) {
        // 删除表
        try {
          resultDdlStmt.execute("DROP TABLE  " + tableName);
        } catch (SQLException e) {
          // in case table does not exist
        }

        // 创建表
        ResultSetMetaData rsmd = rs.getMetaData();
        int columns = rsmd.getColumnCount();
        StringBuffer sqlCreate = new StringBuffer(20);
        sqlCreate.append("CREATE TABLE ");
        sqlCreate.append(tableName);
        sqlCreate.append(" (");
        sqlCreate.append("\n\t");
        sqlCreate.append("uuid");
        sqlCreate.append(" ");
        sqlCreate.append("VARCHAR(38)");
        for (int i = 1; i <= columns; i++) {
          sqlCreate.append(" null,");
          sqlCreate.append("\n\t");
          String name = rsmd.getColumnLabel(i);// 获取列的名称
          String type = getTypeName(rsmd.getColumnType(i));// 获取列的类型
          if (type.equals("CHAR") || type.equals("VARCHAR") || type.equals("LONGVARCHAR")
              || type.equals("VARCHAR2")) {
            sqlCreate.append(name);
            sqlCreate.append(" ");
            sqlCreate.append(type);
            sqlCreate.append("(");
            sqlCreate.append((int) (rsmd.getColumnDisplaySize(i) * 1.5));
            sqlCreate.append(")");
          } else if (type.equals("DECIMAL") || type.equals("NUMERIC") || type.equals("NUMBER")
              || type.equals("BIGDECIMAL")) {
            int precision = rsmd.getPrecision(i);
            int scale = rsmd.getScale(i);
            // 计算字段的precision和scale都是0, 我们把它设成(24, 4). 有没有更好的办法?
            if (precision == 0) {
              precision = 24;
              scale = 4;
            }
            sqlCreate.append(name);
            sqlCreate.append(" ");
            sqlCreate.append(type);
            sqlCreate.append("(");
            sqlCreate.append(precision);
            sqlCreate.append(",");
            sqlCreate.append(scale);
            sqlCreate.append(")");
          } else {
            sqlCreate.append(name);
            sqlCreate.append(" ");
            sqlCreate.append(type);
          }
        }
        sqlCreate.append(" null)");
        if (sourceDbType == ObjectUtil.dbtypeMySql)
          sqlCreate.append("ENGINE=MyISAM DEFAULT CHARSET=gbk");
        logger.debug(sqlCreate.toString());
        resultDdlStmt.execute(sqlCreate.toString());

        // 插入数据
        StringBuffer sqlInsert = new StringBuffer(20);
        sqlInsert.append("INSERT INTO ");
        sqlInsert.append(tableName);
        sqlInsert.append(" VALUES (");
        for (int i = 0; i <= columns; i++) {
          if (i > 0)
            sqlInsert.append(",");
          sqlInsert.append("?");
        }
        sqlInsert.append(")");
        logger.debug(sqlInsert.toString());
        resultInsertStmt = resultConnection.prepareStatement(sqlInsert.toString());
        while (!bStop && rs.next()) {
          try {
            Object cc = (Object) StringUtil.createUuid();
            resultInsertStmt.setObject(1, cc);
          } catch (Exception e) {
          }
          for (int i = 1; i <= columns; i++) {
            Object a = null;
            try {
              a = rs.getObject(i);
              if (a == null)
                resultInsertStmt.setNull(i + 1, rsmd.getColumnType(i));
              else
                resultInsertStmt.setObject(i + 1, a);
            } catch (SQLException e) {
              logger.warn("saveResult: resultInsertStmt.setObject(" + (i + 1) + ", " + a + ")");
              if (!bStop)
                throw e;
            }
          }
          try {
            resultInsertStmt.executeUpdate();
          } catch (SQLException t) {
            logger.error(t);
            for (int i = 1; i <= columns; i++) {
              logger.error(i + ": " + rs.getObject(i));
            }
            throw t;
          }
          resultCount++;
          if (resultCount % 100 == 0)
            resultConnection.commit();
        }
        resultConnection.commit();
        if (bStop) {
          try {
            resultDdlStmt.execute("DROP TABLE  " + tableName);
          } catch (SQLException e) {
            // in case table does not exist
          }
        }
      }
    } finally {
      if (rs != null)
        rs.close();
      if (stmt != null)
        stmt.close();
      if (resultDdlStmt != null)
        resultDdlStmt.close();
      if (resultInsertStmt != null)
        resultInsertStmt.close();
    }
    return resultCount;
  }

  public void cancel() throws SQLException {
    if (bStop)
      return;
    bStop = true;
    if (stmt != null)
      synchronized (stmt) {
        stmt.cancel();
        stmt.close();
      }
  }

  /**
   * 给SQLScript使用
   * 
   * @return
   */
  public boolean isCancelled() {
    return bStop;
  }

  /**
   * 给SQLScript使用, 保证它用这个stmt, 以便能够中断
   * 
   * @return
   */
  public Statement getStatement() {
    return stmt;
  }

  public static String getTypeName(int sqlType) {
    return (String) typeMap.get(new Integer(sqlType));
  }

  private static HashMap2 oracle = new HashMap2().put2(new Integer(Types.BIT), "BIT").put2(
      new Integer(Types.TINYINT), "INTEGER").put2(new Integer(Types.SMALLINT), "INTEGER").put2(
      new Integer(Types.INTEGER), "INTEGER").put2(new Integer(Types.BIGINT), "INTEGER").put2(
      new Integer(Types.FLOAT), "FLOAT").put2(new Integer(Types.REAL), "REAL").put2(
      new Integer(Types.DOUBLE), "REAL").put2(new Integer(Types.NUMERIC), "NUMBER").put2(
      new Integer(Types.DECIMAL), "DECIMAL").put2(new Integer(Types.CHAR), "CHAR").put2(
      new Integer(Types.VARCHAR), "VARCHAR2").put2(new Integer(Types.LONGVARCHAR), "VARCHAR2")
      .put2(new Integer(Types.DATE), "DATE").put2(new Integer(Types.TIME), "DATE").put2(
          new Integer(Types.TIMESTAMP), "TIMESTAMP").put2(new Integer(Types.BINARY), "RAW").put2(
          new Integer(Types.VARBINARY), "BLOB").put2(new Integer(Types.LONGVARBINARY), "BLOB")
      .put2(new Integer(Types.BLOB), "BLOB").put2(new Integer(Types.CLOB), "CLOB").put2(
          new Integer(Types.BOOLEAN), "INTEGER");

  private static HashMap2 mssql = new HashMap2().put2(new Integer(Types.BIT), "BIT").put2(
      new Integer(Types.TINYINT), "TINYINT").put2(new Integer(Types.SMALLINT), "SMALLINT").put2(
      new Integer(Types.INTEGER), "INT").put2(new Integer(Types.BIGINT), "BIGINT").put2(
      new Integer(Types.FLOAT), "FLOAT").put2(new Integer(Types.REAL), "REAL").put2(
      new Integer(Types.DOUBLE), "REAL").put2(new Integer(Types.NUMERIC), "NUMERIC").put2(
      new Integer(Types.DECIMAL), "DECIMAL").put2(new Integer(Types.CHAR), "CHAR").put2(
      new Integer(Types.VARCHAR), "VARCHAR").put2(new Integer(Types.LONGVARCHAR), "VARCHAR").put2(
      new Integer(Types.DATE), "DATETIME").put2(new Integer(Types.TIME), "DATETIME").put2(
      new Integer(Types.TIMESTAMP), "DATETIME").put2(new Integer(Types.BINARY), "BINARY").put2(
      new Integer(Types.VARBINARY), "VARBINARY")
      .put2(new Integer(Types.LONGVARBINARY), "VARBINARY").put2(new Integer(Types.BLOB),
          "VARBINARY").put2(new Integer(Types.CLOB), "NVARCHAR").put2(new Integer(Types.BOOLEAN),
          "INT");

  private static HashMap2 mysql = new HashMap2().put2(new Integer(Types.BIT), "TINYINT").put2(
      new Integer(Types.TINYINT), "TINYINT").put2(new Integer(Types.SMALLINT), "SMALLINT").put2(
      new Integer(Types.INTEGER), "INT").put2(new Integer(Types.BIGINT), "BIGINT").put2(
      new Integer(Types.FLOAT), "FLOAT").put2(new Integer(Types.REAL), "DOUBLE").put2(
      new Integer(Types.DOUBLE), "DOUBLE").put2(new Integer(Types.NUMERIC), "NUMERIC").put2(
      new Integer(Types.DECIMAL), "DECIMAL").put2(new Integer(Types.CHAR), "CHAR").put2(
      new Integer(Types.VARCHAR), "VARCHAR").put2(new Integer(Types.LONGVARCHAR), "VARCHAR").put2(
      new Integer(Types.DATE), "DATE").put2(new Integer(Types.TIME), "TIME").put2(
      new Integer(Types.TIMESTAMP), "TIMESTAMP").put2(new Integer(Types.BINARY), "TINYBLOB").put2(
      new Integer(Types.VARBINARY), "BLOB").put2(new Integer(Types.LONGVARBINARY), "LONGBLOB")
      .put2(new Integer(Types.BLOB), "BLOB").put2(new Integer(Types.CLOB), "VARCHAR").put2(
          new Integer(Types.BOOLEAN), "INT");

  private static HashMap2 typeMap;
  private static int sourceDbType;
  static {
    try {
      try {
        sourceDbType = ObjectUtil.getDbType(QueryResultSession.getSession().connection());
      } catch (Exception e) {
      } finally {
        QueryResultSession.closeSession();
      }
      if (sourceDbType == ObjectUtil.dbtypeMsSql)
        typeMap = mssql;
      else if (sourceDbType == ObjectUtil.dbtypeMySql)
        typeMap = mysql;
      else if (sourceDbType == ObjectUtil.dbtypeOracle)
        typeMap = oracle;
    } catch (Exception e) {
    }
  }

  public IDomain getCurrentDomain() {
    return currentDomain;
  }

  public void setCurrentDomain(IDomain currentDomain) {
    this.currentDomain = currentDomain;
  }

}
